package br.com.skytef.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;
import java.util.ListIterator;

import javax.servlet.http.HttpSession;

import br.com.skytef.jdbc.ConnectionFactory;
import br.com.skytef.utils.Dates;
import br.com.skytef.utils.utils;

public class ReportDao {
	public Connection connection;
	
	public ReportDao(){
		this.connection = new ConnectionFactory().getConnection();
	}
	
	public ReportDao(HttpSession session){
		this.connection = utils.recoverSessionDBConnection(session);
	}
	
	public ReportDao(Connection con){
		this.connection = con;
	}
	
	public Connection getConnection(){
		return connection;
	}

	public List<List<String>> selectIdOrderPrint(boolean group, Date date) throws Exception {
		List<Integer> ids = new ArrayList<Integer>();

		Connection connection = new ConnectionFactory().getConnection();
		PreparedStatement stmt = connection.prepareStatement(this.getQrySelectRepHour());

		List<List<String>> data = new ArrayList<List<String>>();
		List<String> line = new ArrayList<String>();
		try {
			
			CallableStatement cs = null;
			cs = connection.prepareCall("{call proc_relatHour(?, ?)}");
			
			cs.setBoolean(1,group);
			cs.setString(2,Dates.dateToStr(date, "yyyy-MM-dd"));
			
			ResultSet rs = cs.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnsNumber = rsmd.getColumnCount();
			
			for (int i = 1; i < columnsNumber+1; i++) {
				line.add(rsmd.getColumnLabel(i));
			}
			data.add(line);
						
			while (rs.next()) {
				line = new ArrayList<String>();
				for (int i = 1; i < columnsNumber+1; i++) {
					line.add(rs.getString(i));
				}
				data.add(line);
			}

			connection.close();
			rs.close();
			stmt.close();
			return data;

		} catch (SQLException e) {
			connection.close();
			throw new RuntimeException(e);
		}
	}

	/*
	 * Query
	 */

	private String getQrySelectRepHour() {
		StringBuffer sql = new StringBuffer();

		sql.append("set @sql = null; ");
		sql.append("select  ");
		sql.append("    group_concat(distinct concat('sum(case when hour(itens.Hora) = ', hour(itens.Hora), ' then itens.Quant else 0 end) as ''', hour(itens.Hora),'hs''')) ");
		sql.append("into @sql ");
		sql.append("from ");
		sql.append("    pedidos_has_produtos itens; ");
		sql.append(" ");
		sql.append("SET @sql = CONCAT( ");
		sql.append("'SELECT merc.DescrMercadoria, ");
		sql.append("        det.DescricaoProd,  ");
		sql.append("', @sql, ");
		sql.append("' from ");
		sql.append("    pedidos ped ");
		sql.append("        left join ");
		sql.append("    pedidos_has_produtos itens ON ped.idPedido = itens.Pedidos_idPedido ");
		sql.append("        left join ");
		sql.append("    mercadorias merc ON itens.Produtos_idProduto = merc.Produtos_idProduto ");
		sql.append("        left join ");
		sql.append("    pedidos_has_produtos_has_detalhes det_pro ON det_pro.pk_IdItem = itens.IdItem ");
		sql.append("        left join ");
		sql.append("    detalhes_produto det ON det.idDetalhes_Produto = det_pro.pk_idDetalhes_Produto ");
		sql.append("group by merc.DescrMercadoria, det.DescricaoProd;'); ");
		sql.append(" ");
		sql.append("prepare stmt from @sql; ");
		sql.append("execute stmt; ");
		sql.append("deallocate prepare stmt; ");

		return sql.toString();
	}

}
